package com.example.springboot.utils.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Random;

import org.junit.Test;

public class JdbcTest {

	@Test
	public void Test() {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = JdbcUtil.getConnection();
			String sql = "INSERT INTO student(id,name,sex,remark,time) VALUES(?,CONCAT('姓名',?),?,?,?)";
			pstm = conn.prepareStatement(sql);
			conn.setAutoCommit(false);
			Long startTime = System.currentTimeMillis();
			Random rand = new Random();
			int a, b, c, d;
			for (int i = 6000001; i <= 7000000; i++) {
				pstm.setString(1, i + "");
				pstm.setString(2, i + "");
				a = rand.nextInt(10);
				b = rand.nextInt(10);
				c = rand.nextInt(10);
				d = rand.nextInt(10);
				pstm.setInt(3, 1);
				pstm.setString(4, "" + "188" + a + "88" + b + c + "66" + d);
				pstm.setDate(5, new Date(System.currentTimeMillis()));
				pstm.addBatch();
				if ((i + 1) % 100000 == 0) {
					// 每执行一次
					// 就清一次sql缓存，否则每一次是执行1000，2000，3000...条sql语句而不是每一次执行1000条。
					pstm.executeBatch();
					pstm.clearBatch();
				}
			}
			pstm.executeBatch();
			conn.commit();
			Long endTime = System.currentTimeMillis();
			System.out.println("OK,用时：" + (endTime - startTime));
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.close(conn, pstm);
		}
	}

	@Test
	public void Test2() {
		Long startTime = System.currentTimeMillis();
		try {
			String sql = "INSERT INTO student(id,name,sex,remark,time) VALUES(?,CONCAT('姓名',?),?,?,?)";
			JdbcUtil.batch(sql, new JdbcUtil.SQLCallBack() {
				@Override
				public void batch(PreparedStatement pstm) throws SQLException {
					Random rand = new Random();
					int a, b, c, d;
					for (int i = 10000000; i < 20000000; i++) {
						pstm.setString(1, i + "");
						pstm.setString(2, i + "");
						a = rand.nextInt(10);
						b = rand.nextInt(10);
						c = rand.nextInt(10);
						d = rand.nextInt(10);
						pstm.setInt(3, 1);
						pstm.setString(4, "" + "188" + a + "88" + b + c + "66" + d);
						pstm.setDate(5, new Date(System.currentTimeMillis()));
						pstm.addBatch();
						if ((i + 1) % 100000 == 0) {
							// 每执行一次
							// 就清一次sql缓存，否则每一次是执行1000，2000，3000...条sql语句而不是每一次执行1000条。
							pstm.executeBatch();
							pstm.clearBatch();
						}
					}
				}
			});
			Long endTime = System.currentTimeMillis();
			System.out.println("OK,用时：" + (endTime - startTime));
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

}
